package org.software.dao;

import org.software.entity.Group;
import org.software.utils.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class GroupDao {
    private DbUtil dbUtil;
    public boolean groupAdd(String groupName,String className){
        dbUtil = new DbUtil();
        Connection conn = dbUtil.getConnection();
        String sql = "insert into `group`(group_name,class_name) value(?,?)";
        String addCountSql = "update class set group_count = group_count+1 where class_name = ?";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,groupName);
            ps.setString(2,className);
            PreparedStatement addCountPs = conn.prepareStatement(addCountSql);
            addCountPs.setString(1,className);
            addCountPs.executeUpdate();
            return ps.executeUpdate()>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }finally {
            dbUtil.close(conn);
        }
    }
    public boolean groupDelete(String groupName){
        dbUtil = new DbUtil();
        Connection conn = dbUtil.getConnection();
        String sql = "delete from `group` where group_name = ?";
        String deleteCountSql = "update class set group_count = group_count-1 where class_name = ?";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,groupName);
            PreparedStatement deleteCountPs = conn.prepareStatement(deleteCountSql);
            deleteCountPs.setString(1,groupName);
            ps.executeUpdate();
            return ps.executeUpdate()>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }finally {
            dbUtil.close(conn);
        }
    }
    public boolean groupUpdate(String groupName,String newGroupName) {
        dbUtil = new DbUtil();
        Connection conn = dbUtil.getConnection();
        String sql = "update `group` set group_name = ? where group_name = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, newGroupName);
            ps.setString(2, groupName);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            dbUtil.close(conn);
        }
    }
    public List<Group> groupQuery(){
        dbUtil = new DbUtil();
        Connection conn = dbUtil.getConnection();
        String sql = "select * from `group`";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            List<Group> list = new ArrayList<>();
            while (rs.next()){
                Group group = new Group();
                group.setGroupId(rs.getInt("group_id"));
                group.setGroupName(rs.getString("group_name"));
                group.setClassName(rs.getString("class_name"));
                group.setStudentCount(rs.getInt("student_count"));
                list.add(group);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            dbUtil.close(conn);
        }
    }
    public List<Group> groupQueryByName(String groupName){
        dbUtil = new DbUtil();
        Connection conn = dbUtil.getConnection();
        String sql = "select * from `group` where group_name like '%"+groupName+"%'";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            Group group = new Group();
            List<Group> list = new ArrayList<>();
            while (rs.next()){
                group.setGroupId(rs.getInt("group_id"));
                group.setGroupName(rs.getString("group_name"));
                group.setStudentCount(rs.getInt("student_count"));
                list.add(group);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            dbUtil.close(conn);
        }
    }
    public List<Group> groupQueryByClassName(String className){
        dbUtil = new DbUtil();
        Connection conn = dbUtil.getConnection();
        String sql = "select * from `group` where class_name = ?";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,className);
            ResultSet rs = ps.executeQuery();
            Group group = new Group();
            List<Group> list = new ArrayList<>();
            while (rs.next()){
                group.setGroupId(rs.getInt("group_id"));
                group.setGroupName(rs.getString("group_name"));
                group.setStudentCount(rs.getInt("student_count"));
                list.add(group);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            dbUtil.close(conn);
        }
    }
}
